#include <thread>
#include "server.h"
#include "Identity.h"

string make_date(time_t t) 
{
	char buffer[100];
	struct tm* time_info = std::gmtime(&t);
	std::strftime(buffer, sizeof(buffer), "%a, %d %b %Y %H:%M:%S GMT", time_info);
	return std::string(buffer);
}


string extractTime(string str)
{
	size_t pos = str.find(':');
	if (pos == string::npos)
	{
		return "";
	}
	
	return str.substr(pos - 2, 8);
}

bool verification(MYSQL* conn, string username, string password)
{
	char cmd[1024];

	sprintf(cmd, "SELECT * FROM student WHERE student_id = %d", atoi(username.c_str()));
	if (mysql_query(conn, cmd))
	{
		printf("Failed to search student info,Error: %s\n", mysql_error(conn));
	}

	MYSQL_RES* res = mysql_store_result(conn);

	MYSQL_ROW row = mysql_fetch_row(res);

	if (res->data->data == NULL)
	{
		return false;
	}
	else if (password != row[2])
	{
		return false;
	}

	return true;
}

vector<string> read_file(string path)
{
	vector<string> t;
	ifstream file(path, ios::binary | ios::ate);
	if (!file.is_open())
	{
		return { "404 NOT FOUND" };
	}

	size_t fileSize = static_cast<size_t>(file.tellg());
	string buffer(fileSize, '\0');
	file.seekg(0, ios::beg);

	char* temp = const_cast<char*>( buffer.data());
	file.read(temp, fileSize);
	file.close();

	string content_type;
	if (path.find("css") != string::npos)
	{
		content_type = "text/css";
	}
	else if(path.find("html"))
	{
		content_type = "text/html";
	}
	else if(path.find("js"))
	{
		content_type = "application/javascript";
	}
	else if (path.find("jpg") || path.find("jpeg"))
	{
		content_type = "image/jpeg";
	}
	t.push_back(buffer);
	t.push_back(content_type);
	return t;
}

int SmatchToInt(string temp)
{
	return atoi(temp.c_str());
}

string cut(string str, int left, int right)
{
	string temp = "";
	for (int i = left; i < right; i++)
	{
		temp += str[i];
	}
	return temp;
}

vector<int> split(string str)
{
	vector<int> result;
	string temp = "";
	for (int i = 0; i < str.size(); i++)
	{
		if (str[i] == ',')
		{
			result.push_back(atoi(temp.c_str()));
			temp = "";
		}
		else
		{
			temp += str[i];
		}
	}
	result.push_back(atoi(temp.c_str()));

	return result;
}

vector<string> split(string str, char del)
{
	vector<string> result;
	string temp = "";
	for (int i = 0; i < str.size(); i++)
	{
		if (str[i] == del)
		{
			result.push_back(temp);
			temp = "";
		}
		else
		{
			temp += str[i];
		}
	}
	result.push_back(temp);

	return result;
}

string Make_Paper(vector<string> subs)
{
	string temp = "";
	for (int i = 0; i < subs.size(); i++)
	{
		temp += subs[i];
	}

	return temp;
}

BanRecord Teacher::ban(int id, string reason) {
	Date date = getLocalDate();
	BanRecord result(id, this->id, date, reason);
	return move(result);
}

//初始化
void MySQL::create_DataBase()
{
	char cmd[1024];

	sprintf(cmd, "create table student(student_id int not null primary key auto_increment,student_name varchar(255)"
		" not null,password varchar(255) not null,profession varchar(255) not null,class_id int not null)");

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to create table student,Error: %s", mysql_error(SQL));
		
	}

	memset(cmd, '\0', sizeof(cmd));

	sprintf(cmd, "create table teacher(teacher_id int not null primary key auto_increment,teacher_name varchar(255)"
		" not null,password varchar(255) not null,subject varchar(255) not null)");

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to create table teacher,Error: %s", mysql_error(SQL));
		
	}

	memset(cmd, '\0', sizeof(cmd));

	sprintf(cmd, "create table objective_sub(objective_sub_code int not null auto_increment"
		" primary key,teacher_id int not null,subject varchar(255) not null,objective_sub_describe"
		" varchar(2048) not null,option_A varchar(1024) not null,option_B varchar(1024)"
		" not null,option_C varchar(1024) not null,option_D varchar(1024) not null,correct_ans int not null)");

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to create table objective_sub,Error: %s", mysql_error(SQL));
		
	}

	memset(cmd, '\0', sizeof(cmd));

	sprintf(cmd, "create table subjective_sub(subjective_sub_code int not null primary"
		" key auto_increment,teacher_id int not null,subject varchar(255)"
		" not null,subjective_sub_describe varchar(2048) not null)");

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to create table subjective_sub,Error: %s", mysql_error(SQL));
		
	}

	memset(cmd, '\0', sizeof(cmd));

	sprintf(cmd, "create table student_ob_ans(student_id int not null,object_sub_code"
		" int not null,option_ans int not null,correct_or_not int not null)");

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to create table student_ob_ans,Error: %s", mysql_error(SQL));
		
	}

	memset(cmd, '\0', sizeof(cmd));

	sprintf(cmd, "create table student_sub_ans(student_id int not null,subject_sub_code"
		" int not null,sub_ans varchar(2048) not null,subject_sub_score int not null)");

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to create table student_sub_ans,Error: %s", mysql_error(SQL));
		
	}

	memset(cmd, '\0', sizeof(cmd));

	sprintf(cmd, "create table object_acc(sub_code int not null,sub_acc"
		" float not null,exam_order int not null)");

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to create table object_acc,Error: %s", mysql_error(SQL));
		
	}

	memset(cmd, '\0', sizeof(cmd));

	sprintf(cmd, "create table subject_acc(sub_code int not null,"
		"sub_acc float not null,exam_order int not null)");

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to create table subject_acc,Error: %s", mysql_error(SQL));
		
	}

	memset(cmd, '\0', sizeof(cmd));

	sprintf(cmd, "create table exam(exam_code int not null primary key auto_increment,"
		"subject_list varchar(2048) not null,student_list varchar(2048) not null,subject"
		" varchar(255) not null,duration int not null,exam_year int not null,exam_month int not"
		" null, exam_day int not null, exam_hour int not null, exam_minute int not null"
		" , exam_second int not null)");

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to create table exam,Error: %s", mysql_error(SQL));
		return;
	}

	memset(cmd, '\0', sizeof(cmd));

	sprintf(cmd, "create table exam_average_score(exam_code int not null primary key"
		" auto_increment,object_average float not null,"
		"subject_average float not null,total_average float not null)");

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to create table exam_average_score,Error: %s", mysql_error(SQL));
		return;
	}

	memset(cmd, '\0', sizeof(cmd));

	sprintf(cmd, "create table student_score(student_id int not null,total_score"
		" int not null,object_score int not null,subject_score int not null,"
		"wrong_sub_code varchar(2048) not null,exam_order int not null)");

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to create table student_score,Error: %s", mysql_error(SQL));
		return;
	}

	memset(cmd, '\0', sizeof(cmd));

	sprintf(cmd, "create table ban_record(student_id int not null,teacher_id"
		" int not null, ban_year int not null,ban_month int not null, ban_day int not null,"
		" ban_hour int not null, ban_minute int not null, ban_second int not null,"
		" reason varchar(2048) not null)");

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to create table ban_record,Error: %s", mysql_error(SQL));
		return;
	}
}

void MySQL::init_sql()
{
	SQL = mysql_init(NULL);
	
	mysql_options(SQL, MYSQL_SET_CHARSET_NAME, "utf8mb4"); // From "Identity.h"

	if (!mysql_real_connect(SQL, host, user, pw, DB_Name, port, NULL, 0))
	{
		printf("Failed to connect to MySQL,Error: %s\n", mysql_error(SQL));
		return;
	}
	char cmd[1024];

	sprintf(cmd, "SELECT * FROM student");

	if (mysql_query(SQL, cmd))
	{
		string error = mysql_error(SQL);
		if (error.find("exist"))
		{
			printf("Detected That You Have No Local DataBase,Program Will CREATE ONE\n");

			create_DataBase();
		}
		else
		{
			cout << error << endl;
		}
	}
	MYSQL_RES* res = mysql_store_result(SQL);
	mysql_free_result(res);
}

void MySQL::insert_sql(Student &stu)
{
	char cmd[1024];

	sprintf(cmd, "INSERT INTO student (student_id,student_name,password,profession,class_id) values(%d,'%s','%s','%s',%d)",
		stu.id, stu.name.c_str(), stu.password.c_str(), stu.pro.c_str(), stu.class_id);

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Insert Student,Error: %s\n", mysql_error(SQL));
	}

	return;
}

void MySQL::insert_sql(Teacher& t)
{
	char cmd[1024];

	sprintf(cmd, "INSERT INTO teacher (teacher_id,teacher_name,password,subject) values(%d,'%s','%s','%s')",
		t.id, t.name.c_str(), t.password.c_str(), t.subject.c_str());

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Insert Teacher,Error: %s\n", mysql_error(SQL));
	}

	return;
}

void MySQL::insert_sql(Object_Sub& t)
{
	char cmd[1024];

	sprintf(cmd, "INSERT INTO objective_sub (objective_sub_code,teacher_id,subject,objective_sub_describe,option_A,option_B,option_C,option_D,correct_ans)"
		" values(%d,%d,'%s','%s','%s','%s','%s','%s',%d)", t.code, t.teacher_id, t.subject.c_str(), t.describe.c_str(), t.Option_A.c_str(), t.Option_B.c_str(), t.Option_C.c_str(), t.Option_D.c_str(), t.ans);

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Insert Objective_sub,Error: %s\n", mysql_error(SQL));
	}

	return;
}

void MySQL::insert_sql(Subject_Sub& t)
{
	char cmd[1024];

	sprintf(cmd, "INSERT INTO subjective_sub (subjective_sub_code,teacher_id,subject,subjective_sub_describe) values(%d,%d,'%s','%s')",
			t.code, t.teacher_id, t.subject.c_str(), t.describe.c_str());

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Insert Subjective_sub,Error: %s\n", mysql_error(SQL));
	}

	return;
}

void MySQL::insert_sql(Student_ob_ans& t)
{
	char cmd[1024];

	sprintf(cmd, "INSERT INTO student_ob_ans (student_id,object_sub_code,option_ans,correct_or_not) values(%d,%d,%d,0)",
		t.stu_id, t.sub_code,t.option_ans);

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Insert Student_ob_ans,Error: %s\n", mysql_error(SQL));
	}

	return;
}

void MySQL::insert_sql(Student_sub_ans& t)
{
	char cmd[1024];

	sprintf(cmd, "INSERT INTO student_sub_ans (student_id,subject_sub_code,sub_ans,subject_sub_score) values(%d,%d,'%s',0)",
		t.stu_id, t.sub_code, t.sub_ans.c_str());

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Insert Student_sub_ans,Error: %s\n", mysql_error(SQL));
	}

	return;
}

void MySQL::insert_sql(Student_score& t)
{
	char cmd[1024];

	sprintf(cmd, "INSERT INTO student_score (student_id,total_score,object_score,subject_score,wrong_sub_code,exam_order) "
		"values(%d,%d,%d,%d,'%s',%d)", 
		t.student_id, t.total_score, t.object_score, t.subject_score, t.wrong_sub_code.c_str(), t.exam_order);

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Insert Student_score,Error: %s\n", mysql_error(SQL));
	}

	return;
}

void MySQL::insert_sql(ob_acc& t)
{
	char cmd[1024];

	sprintf(cmd, "INSERT INTO object_acc (sub_code,sub_acc,exam_order) values(%d,%f,%d)", t.code, t.acc, t.order);

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Insert Object_acc,Error: %s\n", mysql_error(SQL));
	}

	return;
}

void MySQL::insert_sql(sub_acc& t)
{
	char cmd[1024];

	sprintf(cmd, "INSERT INTO subject_acc (sub_code,sub_acc,exam_order) values(%d,%f,%d)", t.code, t.acc, t.order);

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Insert Subject_acc,Error: %s\n", mysql_error(SQL));
	}

	return;
}

//添加信息
void MySQL::insert_sql(Exam& t)
{
	char cmd[1024];

	sprintf(cmd, "INSERT INTO exam (exam_code,subject_list,student_list,subject,duration,exam_year,"
		"exam_month,exam_day,exam_hour,exam_minute) values(%d,'%s','%s','%s',%d,%d,%d,%d,%d,%d)",
		t.exam_code, t.sub_list.c_str(), t.student_list.c_str(), t.subject.c_str(), t.duration, t.exam_date.year,
		t.exam_date.month, t.exam_date.day, t.exam_date.hour, t.exam_date.minute);

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Insert Subject_acc,Error: %s\n", mysql_error(SQL));
	}

	return;
}

void MySQL::insert_sql(exam_aver& t)
{
	char cmd[1024];

	sprintf(cmd, "INSERT INTO exam_average_score (exam_code,object_average,subject_average,total_average)"
		" values(%d,%f,%f,%f)", t.order, t.ob_aver, t.sub_aver, t.total_aver);

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Insert Exam_Average_Score,Error: %s\n", mysql_error(SQL));
	}

	return;
}

void MySQL::insert_sql(BanRecord& t)
{
	char cmd[1024];

	sprintf(cmd, "INSERT INTO ban_record (student_id,teacher_id,ban_year,ban_month,ban_day,ban_hour,"
		"ban_minute,ban_second,reason) values(%d,%d,%d,%d,%d,%d,%d,%d,'%s')", t.stu_id, t.tea_id, t.ban_date.year, t.ban_date.month, t.ban_date.day,
		t.ban_date.hour, t.ban_date.minute, t.ban_date.second, t.reason.c_str());

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Insert Ban Record,Error: %s\n", mysql_error(SQL));
	}

	return;
}

void MySQL::delete_stu(int id)
{
	char cmd[1024];

	sprintf(cmd, "DELETE FROM student WHERE student_id=%d", id);

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Delete Student,Error: %s\n", mysql_error(SQL));
	}

	return;
}

void MySQL::delete_stu()
{
	char cmd[1024];

	sprintf(cmd, "TRUNCATE TABLE student");

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to empty Student");
	}

	return;
}

void MySQL::delete_tea(int id)
{
	char cmd[1024];

	sprintf(cmd, "DELETE FROM teacher WHERE teacher_id=%d", id);

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Delete Teacher,Error: %s\n", mysql_error(SQL));
	}

	return;
}

void MySQL::delete_tea()
{
	char cmd[1024];

	sprintf(cmd, "TRUNCATE TABLE teacher");

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to empty Teacher");
	}

	return;
}

void MySQL::delete_ob(int code)
{
	char cmd[1024];

	sprintf(cmd, "DELETE FROM objective_sub WHERE objective_sub_code=%d", code);

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Delete Objective_sub,Error: %s\n", mysql_error(SQL));
	}

	return;
}

void MySQL::delete_sub(int code)
{
	char cmd[1024];

	sprintf(cmd, "DELETE FROM subjective_sub WHERE subjective_sub_code=%d", code);

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Delete Subjective_sub,Error: %s\n", mysql_error(SQL));
	}

	return;
}

void MySQL::delete_ob_ans()
{
	char cmd[1024];

	sprintf(cmd, "TRUNCATE TABLE student_ob_ans");

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to empty Student_ob_ans");
	}

	return;
}

void MySQL::delete_sub_ans()
{
	char cmd[1024];

	sprintf(cmd, "TRUNCATE TABLE student_sub_ans");

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to empty Student_sub_ans");
	}

	return;
}

void MySQL::delete_exam(int order)
{
	char cmd[1024];

	sprintf(cmd, "DELETE FROM exam WHERE exam_code = %d", order);

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Delete Exam");
	}

	return;
}

void MySQL::delete_ban_record(int id)
{
	char cmd[1024];

	sprintf(cmd, "DELETE FROM ban_record WHERE student_id = %d", id);

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Delete Ban Record");
	}

	return;
}

//修改信息
void MySQL::modify_sql(Student& stu)
{
	char cmd[1024];

	sprintf(cmd, "UPDATE student SET student_name='%s',password='%s',profession='%s',class_id=%d"
		" WHERE student_id=%d",stu.name.c_str(), stu.password.c_str(), stu.pro.c_str(), stu.class_id, stu.id);

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Modify Student,Error: %s\n", mysql_error(SQL));
	}

	return;
}

void MySQL::modify_sql(Teacher& t)
{
	char cmd[1024];

	sprintf(cmd, "UPDATE teacher SET teacher_name='%s',password='%s',subject='%s'"
		" WHERE teacher_id=%d", t.name.c_str(), t.password.c_str(), t.subject.c_str(), t.id);

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Modify Teacher,Error: %s\n", mysql_error(SQL));
	}

	return;
}

void MySQL::modify_sql(Object_Sub& t)
{
	char cmd[1024];

	sprintf(cmd, "UPDATE objective_sub SET "
		"subject='%s',objective_sub_describe='%s',option_A='%s',option_B='%s',option_C='%s',option_D='%s',correct_ans='%d'"
		" WHERE objective_sub_code=%d", t.subject.c_str(), t.describe.c_str(), t.Option_A.c_str(), t.Option_B.c_str(), t.Option_C.c_str(),
		t.Option_D.c_str(), t.ans, t.code);

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Modify Objective_sub,Error: %s\n", mysql_error(SQL));
	}

	return;
}

void MySQL::modify_sql(Subject_Sub& t)
{
	char cmd[1024];
	sprintf(cmd, "UPDATE subjective_sub SET subject = '%s',subjective_sub_describe = '%s' WHERE subjective_sub_code = %d",
		t.subject.c_str(), t.describe.c_str(), t.code);

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Modify Subjective_sub,Error: %s\n", mysql_error(SQL));
	}

	return;
}

//查找信息
vector<Student> MySQL::search_stu(string str)
{
	MYSQL_RES* res = mysql_store_result(SQL);
	if (res != NULL)
	{
		mysql_free_result(res);
	}
	vector<Student> StuList;

	char cmd[1024];

	sprintf(cmd, "SELECT * FROM student WHERE student_name like '%s'", str.c_str());

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Search Student,Error: %s\n", mysql_error(SQL));
		return {};
	}

	res = mysql_store_result(SQL);

	MYSQL_ROW row;
	while (row = mysql_fetch_row(res))
	{
		Student t;
		t.id = atoi(row[0]);
		t.name = row[1];
		t.password = row[2];
		t.pro = row[3];
		t.class_id = atoi(row[4]);
		StuList.push_back(t);
	}
	mysql_free_result(res);
	return StuList;
}

vector<Student> MySQL::search_stu(int id)
{
	MYSQL_RES* res = mysql_store_result(SQL);
	if (res != NULL)
	{
		mysql_free_result(res);
	}
	vector<Student> StuList;

	char cmd[1024];

	sprintf(cmd, "SELECT * FROM student WHERE student_id = %d", id);

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Search Student,Error: %s\n", mysql_error(SQL));
		return {};
	}

	res = mysql_store_result(SQL);

	MYSQL_ROW row;
	while (row = mysql_fetch_row(res))
	{
		Student t;
		t.id = atoi(row[0]);
		t.name = row[1];
		t.password = row[2];
		t.pro = row[3];
		t.class_id = atoi(row[4]);
		StuList.push_back(t);
	}
	mysql_free_result(res);
	return StuList;
}

vector<Student> MySQL::search_stu()
{
	MYSQL_RES* res = mysql_store_result(SQL);
	if (res != NULL)
	{
		mysql_free_result(res);
	}
	vector<Student> StuList;

	char cmd[1024];

	sprintf(cmd, "SELECT * FROM student");

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Search Student,Error: %s\n", mysql_error(SQL));
		return {};
	}

	res = mysql_store_result(SQL);

	MYSQL_ROW row;
	while (row = mysql_fetch_row(res))
	{
		Student t;
		t.id = atoi(row[0]);
		t.name = row[1];
		t.password = row[2];
		t.pro = row[3];
		t.class_id = atoi(row[4]);
		StuList.push_back(t);
	}
	mysql_free_result(res);
	return StuList;
}

vector<Teacher> MySQL::search_tea(string str)
{
	MYSQL_RES* res = mysql_store_result(SQL);
	if (res != NULL)
	{
		mysql_free_result(res);
	}

	vector<Teacher> tlist;
	char cmd[1024];

	sprintf(cmd, "SELECT * FROM teacher WHERE teacher_name like '%s'", str.c_str());

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Search Teacher,Error: %s\n", mysql_error(SQL));
		return {};
	}

	res = mysql_store_result(SQL);

	MYSQL_ROW row;
	while (row = mysql_fetch_row(res))
	{
		Teacher t;
		t.id = atoi(row[0]);
		t.name = row[1];
		t.password = row[2];
		t.subject = row[3];
		tlist.push_back(t);
	}
	mysql_free_result(res);
	return tlist;
}

vector<Teacher> MySQL::search_tea(int id)
{
	MYSQL_RES* res = mysql_store_result(SQL);
	if (res != NULL)
	{
		mysql_free_result(res);
	}
	vector<Teacher> tlist;
	char cmd[1024];

	sprintf(cmd, "SELECT * FROM teacher WHERE teacher_id = %d", id);

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Search Teacher,Error: %s\n", mysql_error(SQL));
		return {};
	}

	res = mysql_store_result(SQL);

	MYSQL_ROW row;
	while (row = mysql_fetch_row(res))
	{
		Teacher t;
		t.id = atoi(row[0]);
		t.name = row[1];
		t.password = row[2];
		t.subject = row[3];
		tlist.push_back(t);
	}
	mysql_free_result(res);
	return tlist;
}

vector<Teacher> MySQL::search_tea()
{
	MYSQL_RES* res = mysql_store_result(SQL);
	if (res != NULL)
	{
		mysql_free_result(res);
	}
	vector<Teacher> tlist;
	char cmd[1024];

	sprintf(cmd, "SELECT * FROM teacher");

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Search Teacher,Error: %s\n", mysql_error(SQL));
		return {};
	}

	res = mysql_store_result(SQL);

	MYSQL_ROW row;
	while (row = mysql_fetch_row(res))
	{
		Teacher t;
		t.id = atoi(row[0]);
		t.name = row[1];
		t.password = row[2];
		t.subject = row[3];
		tlist.push_back(t);
	}
	mysql_free_result(res);
	return tlist;
}

vector<Object_Sub> MySQL::search_ob(int code)
{
	MYSQL_RES* res = mysql_store_result(SQL);
	if (res != NULL)
	{
		mysql_free_result(res);
	}
	vector<Object_Sub> tlist;
	char cmd[1024];

	sprintf(cmd, "SELECT * FROM objective_sub WHERE objective_sub_code = %d", code);

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Search Objective_sub,Error: %s\n", mysql_error(SQL));
		return {};
	}

	res = mysql_store_result(SQL);

	MYSQL_ROW row;
	while (row = mysql_fetch_row(res))
	{
		Object_Sub t;
		t.code = atoi(row[0]);
		t.teacher_id = atoi(row[1]);
		t.subject = row[2];
		t.describe = row[3];
		t.Option_A = row[4];
		t.Option_B = row[5];
		t.Option_C = row[6];
		t.Option_D = row[7];
		t.ans = atoi(row[8]);
		tlist.push_back(t);
	}
	mysql_free_result(res);
	return tlist;
}

vector<Object_Sub> MySQL::search_ob(string str)
{
	MYSQL_RES* res = mysql_store_result(SQL);
	if (res != NULL)
	{
		mysql_free_result(res);
	}
	vector<Object_Sub> tlist;
	char cmd[1024];

	sprintf(cmd, "SELECT * FROM objective_sub WHERE subject like '%s'", str.c_str());

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Search Objective_sub,Error: %s\n", mysql_error(SQL));
		return {};
	}

	res = mysql_store_result(SQL);

	MYSQL_ROW row;
	while (row = mysql_fetch_row(res))
	{
		Object_Sub t;
		t.code = atoi(row[0]);
		t.teacher_id = atoi(row[1]);
		t.subject = row[2];
		t.describe = row[3];
		t.Option_A = row[4];
		t.Option_B = row[5];
		t.Option_C = row[6];
		t.Option_D = row[7];
		t.ans = atoi(row[8]);
		tlist.push_back(t);
	}
	mysql_free_result(res);
	return tlist;
}

vector<Object_Sub> MySQL::search_ob_by_id(int id)
{
	MYSQL_RES* res = mysql_store_result(SQL);
	if (res != NULL)
	{
		mysql_free_result(res);
	}
	vector<Object_Sub> tlist;
	char cmd[1024];

	sprintf(cmd, "SELECT * FROM objective_sub WHERE teacher_id = %d", id);

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Search Objective_sub,Error: %s\n", mysql_error(SQL));
		return {};
	}

	res = mysql_store_result(SQL);

	MYSQL_ROW row;
	while (row = mysql_fetch_row(res))
	{
		Object_Sub t;
		t.code = atoi(row[0]);
		t.teacher_id = atoi(row[1]);
		t.subject = row[2];
		t.describe = row[3];
		t.Option_A = row[4];
		t.Option_B = row[5];
		t.Option_C = row[6];
		t.Option_D = row[7];
		t.ans = atoi(row[8]);
		tlist.push_back(t);
	}
	mysql_free_result(res);
	return tlist;
}

vector<Subject_Sub> MySQL::search_sub(int code)
{
	MYSQL_RES* res = mysql_store_result(SQL);
	if (res != NULL)
	{
		mysql_free_result(res);
	}
	vector<Subject_Sub> tlist;
	char cmd[1024];

	sprintf(cmd, "SELECT * FROM subjective_sub WHERE subjective_sub_code = %d", code);

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Search Subjective_sub,Error: %s\n", mysql_error(SQL));
		return {};
	}

	res = mysql_store_result(SQL);

	MYSQL_ROW row;
	while (row = mysql_fetch_row(res))
	{
		Subject_Sub t;
		t.code = atoi(row[0]);
		t.teacher_id = atoi(row[1]);
		t.subject = row[2];
		t.describe = row[3];
		tlist.push_back(t);
	}
	mysql_free_result(res);
	return tlist;
}

vector<Subject_Sub> MySQL::search_sub(string str)
{
	MYSQL_RES* res = mysql_store_result(SQL);
	if (res != NULL)
	{
		mysql_free_result(res);
	}
	vector<Subject_Sub> tlist;
	char cmd[1024];

	sprintf(cmd, "SELECT * FROM subjective_sub WHERE subject like '%s'", str.c_str());

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Search Subjective_sub,Error: %s\n", mysql_error(SQL));
		return {};
	}

	res = mysql_store_result(SQL);

	MYSQL_ROW row;
	while (row = mysql_fetch_row(res))
	{
		Subject_Sub t;
		t.code = atoi(row[0]);
		t.teacher_id = atoi(row[1]);
		t.subject = row[2];
		t.describe = row[3];
		tlist.push_back(t);
	}
	mysql_free_result(res);
	return tlist;
}

vector<Subject_Sub> MySQL::search_sub_by_id(int id)
{
	MYSQL_RES* res = mysql_store_result(SQL);
	if (res != NULL)
	{
		mysql_free_result(res);
	}
	vector<Subject_Sub> tlist;
	char cmd[1024];

	sprintf(cmd, "SELECT * FROM subjective_sub WHERE teacher_id = %d", id);

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Search Subjective_sub,Error: %s\n", mysql_error(SQL));
		return {};
	}

	res = mysql_store_result(SQL);

	MYSQL_ROW row;
	while (row = mysql_fetch_row(res))
	{
		Subject_Sub t;
		t.code = atoi(row[0]);
		t.teacher_id = atoi(row[1]);
		t.subject = row[2];
		t.describe = row[3];
		tlist.push_back(t);
	}
	mysql_free_result(res);
	return tlist;
}

vector<Student_ob_ans> MySQL::search_ob_ans(int id)
{
	MYSQL_RES* res = mysql_store_result(SQL);
	if (res != NULL)
	{
		mysql_free_result(res);
	}
	vector<Student_ob_ans> tlist;
	char cmd[1024];

	sprintf(cmd, "SELECT * FROM student_ob_ans WHERE student_id = %d", id);

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Search Student_ob_ans,Error: %s\n", mysql_error(SQL));
		return {};
	}

	res = mysql_store_result(SQL);

	MYSQL_ROW row;
	while (row = mysql_fetch_row(res))
	{
		Student_ob_ans t;
		t.stu_id = atoi(row[0]);
		t.sub_code = atoi(row[1]);
		t.option_ans = atoi(row[2]);
		t.correct = atoi(row[3]);
		tlist.push_back(t);
	}
	mysql_free_result(res);
	return tlist;
}

vector<Student_ob_ans> MySQL::search_ob_ans()
{
	MYSQL_RES* res = mysql_store_result(SQL);
	if(res != NULL)
	{
		mysql_free_result(res);
	}
	vector<Student_ob_ans> tlist;
	char cmd[1024];

	sprintf(cmd, "SELECT * FROM student_ob_ans");

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Search Student_ob_ans,Error: %s\n", mysql_error(SQL));
		return {};
	}

	res = mysql_store_result(SQL);

	MYSQL_ROW row;
	while (row = mysql_fetch_row(res))
	{
		Student_ob_ans t;
		t.stu_id = atoi(row[0]);
		t.sub_code = atoi(row[1]);
		t.option_ans = atoi(row[2]);
		t.correct = atoi(row[3]);
		tlist.push_back(t);
	}
	mysql_free_result(res);
	return tlist;
}

vector<Student_sub_ans> MySQL::search_sub_ans(int id)
{
	MYSQL_RES* res = mysql_store_result(SQL);
	if (res != NULL)
	{
		mysql_free_result(res);
	}
	vector<Student_sub_ans> tlist;
	char cmd[1024];

	sprintf(cmd, "SELECT * FROM student_sub_ans WHERE student_id = %d", id);

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Search Student_sub_ans,Error: %s\n", mysql_error(SQL));
		return {};
	}

	res = mysql_store_result(SQL);

	MYSQL_ROW row;
	while (row = mysql_fetch_row(res))
	{
		Student_sub_ans t;
		t.stu_id = atoi(row[0]);
		t.sub_code = atoi(row[1]);
		t.sub_ans = row[2];
		t.score = atoi(row[3]);
		tlist.push_back(t);
	}
	mysql_free_result(res);
	return tlist;
}

vector<Student_sub_ans> MySQL::search_sub_ans()
{
	MYSQL_RES* res = mysql_store_result(SQL);
	if (res != NULL)
	{
		mysql_free_result(res);
	}
	vector<Student_sub_ans> tlist;
	char cmd[1024];

	sprintf(cmd, "SELECT * FROM student_sub_ans");

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Search Student_sub_ans,Error: %s\n", mysql_error(SQL));
		return {};
	}

	res = mysql_store_result(SQL);

	MYSQL_ROW row;
	while (row = mysql_fetch_row(res))
	{
		Student_sub_ans t;
		t.stu_id = atoi(row[0]);
		t.sub_code = atoi(row[1]);
		t.sub_ans = row[2];
		t.score = atoi(row[3]);
		tlist.push_back(t);
	}
	mysql_free_result(res);
	return tlist;
}

vector<Student_score> MySQL::search_student_score(int id)
{
	MYSQL_RES* res = mysql_store_result(SQL);
	if (res != NULL)
	{
		mysql_free_result(res);
	}
	vector<Student_score> tlist;
	char cmd[1024];

	sprintf(cmd, "SELECT * FROM student_score WHERE student_id = %d", id);

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Search Student_score,Error: %s\n", mysql_error(SQL));
		return {};
	}

	res = mysql_store_result(SQL);

	MYSQL_ROW row;
	while (row = mysql_fetch_row(res))
	{
		Student_score t;
		t.student_id = atoi(row[0]);
		t.total_score = atoi(row[1]);
		t.object_score = atoi(row[2]);
		t.subject_score = atoi(row[3]);
		t.wrong_sub_code = row[4];
		t.exam_order = atoi(row[5]);
		tlist.push_back(t);
	}
	mysql_free_result(res);
	return tlist;
}

vector<Student_score> MySQL::search_student_score_by_order(int order)
{
	MYSQL_RES* res = mysql_store_result(SQL);
	if (res != NULL)
	{
		mysql_free_result(res);
	}
	vector<Student_score> tlist;
	char cmd[1024];

	sprintf(cmd, "SELECT * FROM student_score WHERE exam_order = %d", order);

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Search Student_score by order,Error: %s\n", mysql_error(SQL));
		return {};
	}

	res = mysql_store_result(SQL);

	MYSQL_ROW row;
	while (row = mysql_fetch_row(res))
	{
		Student_score t;
		t.student_id = atoi(row[0]);
		t.total_score = atoi(row[1]);
		t.object_score = atoi(row[2]);
		t.subject_score = atoi(row[3]);
		t.wrong_sub_code = row[4];
		t.exam_order = atoi(row[5]);
		tlist.push_back(t);
	}
	mysql_free_result(res);
	return tlist;
}

vector<Exam> MySQL::search_exam(int id)
{
	MYSQL_RES* res = mysql_store_result(SQL);
	if (res != NULL)
	{
		mysql_free_result(res);
	}
	vector<Exam> tlist;
	char cmd[1024];

	sprintf(cmd, "SELECT * FROM exam WHERE exam_code = %d", id);

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Search Exam,Error: %s\n", mysql_error(SQL));
		return {};
	}

	res = mysql_store_result(SQL);

	MYSQL_ROW row;
	while (row = mysql_fetch_row(res))
	{
		Exam t;
		t.exam_code = atoi(row[0]);
		t.sub_list = row[1];
		t.student_list = row[2];
		t.subject = row[3];
		t.duration = atoi(row[4]);
		t.exam_date.year = atoi(row[5]);
		t.exam_date.month = atoi(row[6]);
		t.exam_date.day = atoi(row[7]);
		t.exam_date.hour = atoi(row[8]);
		t.exam_date.minute = atoi(row[9]);
		tlist.push_back(t);
	}

	return tlist;
}

vector<Exam> MySQL::search_exam()
{
	MYSQL_RES* res = mysql_store_result(SQL);
	if (res != NULL)
	{
		mysql_free_result(res);
	}
	vector<Exam> tlist;
	char cmd[1024];

	sprintf(cmd, "SELECT * FROM exam");

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Search Exam,Error: %s\n", mysql_error(SQL));
		return {};
	}

	res = mysql_store_result(SQL);

	MYSQL_ROW row;
	while (row = mysql_fetch_row(res))
	{
		Exam t;
		t.exam_code = atoi(row[0]);
		t.sub_list = row[1];
		t.student_list = row[2];
		t.subject = row[3];
		t.duration = atoi(row[4]);
		t.exam_date.year = atoi(row[5]);
		t.exam_date.month = atoi(row[6]);
		t.exam_date.day = atoi(row[7]);
		t.exam_date.hour = atoi(row[8]);
		t.exam_date.minute = atoi(row[9]);
		tlist.push_back(t);
	}

	return tlist;
}

vector<ob_acc> MySQL::search_ob_acc(int order, int code)
{
	MYSQL_RES* res = mysql_store_result(SQL);
	if (res != NULL)
	{
		mysql_free_result(res);
	}
	vector<ob_acc> tlist;
	char cmd[1024];

	sprintf(cmd, "SELECT * FROM object_acc WHERE exam_order = %d and sub_code = %d", order, code);

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Search Object_Acc,Error: %s\n", mysql_error(SQL));
		return {};
	}

	res = mysql_store_result(SQL);

	MYSQL_ROW row;
	while (row = mysql_fetch_row(res))
	{
		ob_acc t;
		t.code = atoi(row[0]);
		t.acc = stof(row[1]);
		t.order = atoi(row[2]);
		tlist.push_back(t);
	}
	mysql_free_result(res);
	return tlist;
}

vector<ob_acc> MySQL::search_ob_acc(int order)
{
	MYSQL_RES* res = mysql_store_result(SQL);
	if (res != NULL)
	{
		mysql_free_result(res);
	}
	vector<ob_acc> tlist;
	char cmd[1024];

	sprintf(cmd, "SELECT * FROM object_acc WHERE exam_order = %d", order);

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Search Object_Acc,Error: %s\n", mysql_error(SQL));
		return {};
	}

	res = mysql_store_result(SQL);

	MYSQL_ROW row;
	while (row = mysql_fetch_row(res))
	{
		ob_acc t;
		t.code = atoi(row[0]);
		t.acc = stof(row[1]);
		t.order = atoi(row[2]);
		tlist.push_back(t);
	}
	mysql_free_result(res);
	return tlist;
}

vector<sub_acc> MySQL::search_sub_acc(int order, int code)
{
	MYSQL_RES* res = mysql_store_result(SQL);
	if (res != NULL)
	{
		mysql_free_result(res);
	}
	vector<sub_acc> tlist;
	char cmd[1024];

	sprintf(cmd, "SELECT * FROM subject_acc WHERE exam_order = %d and sub_code = %d", order, code);

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Search Subject_Acc,Error: %s\n", mysql_error(SQL));
		return {};
	}

	res = mysql_store_result(SQL);

	MYSQL_ROW row;
	while (row = mysql_fetch_row(res))
	{
		sub_acc t;
		t.code = atoi(row[0]);
		t.acc = stof(row[1]);
		t.order = atoi(row[2]);
		tlist.push_back(t);
	}
	mysql_free_result(res);
	return tlist;
}

vector<sub_acc> MySQL::search_sub_acc(int order)
{
	MYSQL_RES* res = mysql_store_result(SQL);
	if (res != NULL)
	{
		mysql_free_result(res);
	}
	vector<sub_acc> tlist;
	char cmd[1024];

	sprintf(cmd, "SELECT * FROM subject_acc WHERE exam_order = %d", order);

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Search Subject_Acc,Error: %s\n", mysql_error(SQL));
		return {};
	}

	res = mysql_store_result(SQL);

	MYSQL_ROW row;
	while (row = mysql_fetch_row(res))
	{
		sub_acc t;
		t.code = atoi(row[0]);
		t.acc = stof(row[1]);
		t.order = atoi(row[2]);
		tlist.push_back(t);
	}
	mysql_free_result(res);
	return tlist;
}

vector<exam_aver> MySQL::search_exam_aver(int order)
{
	MYSQL_RES* res = mysql_store_result(SQL);
	if (res != NULL)
	{
		mysql_free_result(res);
	}
	vector<exam_aver> tlist;
	char cmd[1024];

	sprintf(cmd, "SELECT * FROM exam_average_score WHERE exam_code = %d", order);

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Search Exam_average_Score,Error: %s\n", mysql_error(SQL));
		return {};
	}

	res = mysql_store_result(SQL);

	MYSQL_ROW row;
	while (row = mysql_fetch_row(res))
	{
		exam_aver t;
		t.order = atoi(row[0]);
		t.ob_aver = stof(row[1]);
		t.sub_aver = stof(row[2]);
		t.total_aver = stof(row[3]);
		tlist.push_back(t);
	}
	mysql_free_result(res);
	return tlist;
}

vector<BanRecord> MySQL::search_ban_record(int id)
{
	MYSQL_RES* res = mysql_store_result(SQL);
	if (res != NULL)
	{
		mysql_free_result(res);
	}
	vector<BanRecord> tlist;
	char cmd[1024];

	sprintf(cmd, "SELECT * FROM ban_record WHERE student_id = %d", id);

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Search Ban Record,Error: %s\n", mysql_error(SQL));
		return {};
	}

	res = mysql_store_result(SQL);

	MYSQL_ROW row;
	while (row = mysql_fetch_row(res))
	{
		BanRecord t(atoi(row[0]), atoi(row[1]), Date(atoi(row[2]), atoi(row[3]), atoi(row[4]), atoi(row[5]), atoi(row[6]), atoi(row[7])), row[8]);
		tlist.push_back(t);
	}

	return tlist;
}

vector<BanRecord> MySQL::search_ban_record_from(int id)
{
	MYSQL_RES* res = mysql_store_result(SQL);
	if (res != NULL)
	{
		mysql_free_result(res);
	}
	vector<BanRecord> tlist;
	char cmd[1024];

	sprintf(cmd, "SELECT * FROM ban_record WHERE teacher_id = %d", id);

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Search Ban Record,Error: %s\n", mysql_error(SQL));
		return {};
	}

	res = mysql_store_result(SQL);

	MYSQL_ROW row;
	while (row = mysql_fetch_row(res))
	{
		BanRecord t(atoi(row[0]), atoi(row[1]), Date(atoi(row[2]), atoi(row[3]), atoi(row[4]), atoi(row[5]), atoi(row[6]), atoi(row[7])), row[8]);
		tlist.push_back(t);
	}

	return tlist;
}

vector<BanRecord> MySQL::search_ban_record()
{
	MYSQL_RES* res = mysql_store_result(SQL);
	if (res != NULL)
	{
		mysql_free_result(res);
	}
	vector<BanRecord> tlist;
	char cmd[1024];

	sprintf(cmd, "SELECT * FROM ban_record");

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to Search Ban Record,Error: %s\n", mysql_error(SQL));
		return {};
	}

	res = mysql_store_result(SQL);

	MYSQL_ROW row;
	while (row = mysql_fetch_row(res))
	{
		BanRecord t(atoi(row[0]), atoi(row[1]), Date(atoi(row[2]), atoi(row[3]), atoi(row[4]), atoi(row[5]), atoi(row[6]), atoi(row[7])), row[8]);
		tlist.push_back(t);
	}

	return tlist;
}

//重设密码
void MySQL::reset_pw(int id)
{
	if (id < 20000)
	{
		char cmd[1024];

		sprintf(cmd, "UPDATE student SET password = '123456' WHERE student_id = %d", id);

		if (mysql_query(SQL, cmd))
		{
			printf("Failed to Reset Password,Error: %s\n", mysql_error(SQL));
			return;
		}
	}
	else
	{
		char cmd[1024];

		sprintf(cmd, "UPDATE teacher SET password = '123456' WHERE teacher_id = %d", id);

		if (mysql_query(SQL, cmd))
		{
			printf("Failed to Reset Password,Error: %s\n", mysql_error(SQL));
			return;
		}
	}

	return;
}

bool MySQL::verify(string username, string password)
{
	int id = atoi(username.c_str());
	vector<Student> stu = search_stu(id);
	if (stu.size() == 0)
	{
		return false;
	}
	else if (stu[0].password != password)
	{
		return false;
	}
	return true;
}

//算分
void MySQL::give_score()
{
	vector<Student_ob_ans> Before_Process = search_ob_ans();
	for (int i = 0; i < Before_Process.size(); i++)
	{
		Student_ob_ans t = Before_Process[i];
		int code = atoi(cut(to_string(t.sub_code), 3, 8).c_str());
		vector<Object_Sub> obsub = search_ob(code);

		if (!obsub.size())
		{
			printf("Object_sub DO NOT Exit");
			return;
		}
		if (t.option_ans == obsub[0].ans)
		{
			char cmd[1024];
			sprintf(cmd, "UPDATE student_ob_ans SET correct_or_not =1 WHERE object_sub_code = %d and student_id=%d;",
				t.sub_code, t.stu_id);

			if (mysql_query(SQL, cmd))
			{
				printf("Failed to give score to object_sub,Error: %s\n", mysql_error(SQL));
			}
		}
	}

	return;
}

void MySQL::give_score(int order, int id, int code, int score)
{
	char cmd[1024];

	int real_code = atoi((to_string(100 + order) + to_string(code)).c_str());

	sprintf(cmd, "UPDATE student_sub_ans SET subject_sub_score=%d WHERE subject_sub_code = %d and student_id = %d", 
		score, real_code, id);

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to give score to subject_sub,Error: %s\n", mysql_error(SQL));
	}

	return;
}

void MySQL::calculate_score(int id,int exam_order)
{
	vector<Student_ob_ans> obans = search_ob_ans(id);
	vector<Student_sub_ans> subans = search_sub_ans(id);
	vector<Student_ob_ans> ob;
	vector<Student_sub_ans> sub;

	int object_score = 0;
	int subject_score = 0;
	int total_socre = 0;
	string wrong_ans = "";

	for (int i = 0; i < obans.size(); i++)
	{
		int order = obans[i].sub_code / 100000 - 100;
		if (exam_order == order)
		{
			ob.push_back(obans[i]);
		}
	}

	for (int i = 0; i < subans.size(); i++)
	{
		int order = subans[i].sub_code / 100000 - 100;
		if (exam_order == order)
		{
			sub.push_back(subans[i]);
		}
	}

	for (int i = 0; i < ob.size(); i++)
	{
		Student_ob_ans t = ob[i];
		object_score += t.correct * 3;
		if (t.correct == 0)
		{
			if(wrong_ans.size()!=0)
			{
				wrong_ans += ",";
			}
			wrong_ans += to_string(t.sub_code - (100 + exam_order) * 100000);
		}
	}

	for (int i = 0; i < sub.size(); i++)
	{
		Student_sub_ans t = sub[i];
		subject_score += t.score;

		if (t.score <= 5)
		{
			if (wrong_ans.size() != 0)
			{
				wrong_ans += ",";
			}
			wrong_ans += to_string(t.sub_code - (100 + exam_order) * 100000);
		}
	}

	total_socre = object_score + subject_score;
	Student_score t = Student_score(id, total_socre, object_score, subject_score, wrong_ans,exam_order);
	insert_sql(t);
}

//生成随机题目列表
string MySQL::generate_sub_list(string subject)
{
	string list = "";
	vector<Object_Sub> oblist= search_ob(subject);
	vector <Subject_Sub>sublist = search_sub(subject);

	mt19937 engine(time(NULL));
	shuffle(oblist.begin(), oblist.end(), engine);
	shuffle(sublist.begin(), sublist.end(), engine);

	for (int i = 0; i < 20; i++)
	{
		if (list.size() != 0)
		{
			list += ',';
		}
		list += to_string(oblist[i].code);
	}

	for (int i = 0; i < 4; i++)
	{
		if (list.size() != 0)
		{
			list += ',';
		}
		list += to_string(sublist[i].code);
	}

	return list;
}

//计算排名
float MySQL::get_rank(int id, int order)
{
	vector<Student_score> total = search_student_score_by_order(order);
	vector<Student_score> target = search_student_score(id);
	int target_score = 0;
	float sum = 0;
	float high = 0;
	for (int i = 0; i < target.size(); i++)
	{
		if (target[i].exam_order = order)
		{
			target_score = target[i].total_score;
		}
	}

	for (int i = 0; i < total.size(); i++)
	{
		sum += 1;
		if (total[i].total_score > target_score)
		{
			high += 1;
		}
	}
	return (high + 1) / sum;
}
//计算得分率
void MySQL::get_acc(int exam_order)
{
	vector<Student_ob_ans> origin_ob = search_ob_ans();
	vector<Student_sub_ans> origin_sub = search_sub_ans();
	vector<Student_ob_ans> ob;
	vector<Student_sub_ans> sub;
	vector<Exam> exam = search_exam(exam_order);
	vector<int> sublist = split(exam[0].sub_list);
	vector<ob_acc> obacclist;
	vector<sub_acc> subacclist;
	int numOfStudent = split(exam[0].student_list).size();

	for (int i = 0; i < origin_ob.size(); i++)
	{
		Student_ob_ans t = origin_ob[i];
		int order = t.sub_code / 100000 - 100;
		if (order == exam_order)
		{
			ob.push_back(t);
		}
	}
	for (int i = 0; i < origin_sub.size(); i++)
	{
		Student_sub_ans t = origin_sub[i];
		int order = t.sub_code / 100000 - 100;
		if (order == exam_order)
		{
			sub.push_back(t);
		}
	}

	for (int i = 0; i < sublist.size(); i++)
	{
		ob_acc t1;
		sub_acc t2;
		if (sublist[i] < 20000)
		{
			t1.code = sublist[i];
			obacclist.push_back(t1);
		}
		else
		{
			t2.code = sublist[i];
			subacclist.push_back(t2);
		}
	}

	for (int i = 0; i < obacclist.size(); i++)
	{
		for (int j = 0; j < ob.size(); j++)
		{
			int real_code = ob[j].sub_code - (100 + exam_order) * 100000;
			if (obacclist[i].code == real_code)
			{
				obacclist[i].acc += ob[j].correct;
			}
		}
	}

	for (int i = 0; i < subacclist.size(); i++)
	{
		for (int j = 0; j < sub.size(); j++)
		{
			int real_code = sub[j].sub_code - (100 + exam_order) * 100000;
			if (subacclist[i].code == real_code)
			{
				subacclist[i].acc += sub[j].score;
			}
		}
	}

	for (int i = 0; i < obacclist.size(); i++)
	{
		obacclist[i].acc /= numOfStudent;
		obacclist[i].order = exam_order;
		insert_sql(obacclist[i]);
	}

	for (int i = 0; i < subacclist.size(); i++)
	{
		subacclist[i].acc /= numOfStudent;
		subacclist[i].order = exam_order;
		insert_sql(subacclist[i]);
	}
}

void MySQL::get_average(int exam_order)
{
	vector<ob_acc> origin_ob = search_ob_acc(exam_order);
	vector<sub_acc> origin_sub = search_sub_acc(exam_order);
	float ob_aver = 0;
	float sub_aver = 0;

	for (int i = 0; i < origin_ob.size(); i++)
	{
		ob_aver += origin_ob[i].acc * float(Objective_sub_score);
	}

	for (int i = 0; i < origin_sub.size(); i++)
	{
		sub_aver += origin_sub[i].acc;
	}

	exam_aver t = exam_aver(exam_order, ob_aver, sub_aver, ob_aver + sub_aver);
	insert_sql(t);
}

int MySQL::generate_sub_code(bool ob_or_sub)
{
	if(!ob_or_sub)
	{
		char cmd[1024];

		sprintf(cmd, "SELECT MAX(objective_sub_code) FROM objective_sub");

		if(mysql_query(SQL,cmd))
		{
			printf("Failed to search Max Objective_sub_code Error: %s",mysql_error(SQL));
		}

		MYSQL_RES *res = mysql_store_result(SQL);

		MYSQL_ROW row;
		while (row = mysql_fetch_row(res))
		{
			return atoi(row[0]) + 1;
		}

		return -1;
	}
	else
	{
		char cmd[1024];

		sprintf(cmd, "SELECT MAX(subjective_sub_code) FROM subjective_sub");

		if (mysql_query(SQL, cmd))
		{
			printf("Failed to search Max Subjective_sub_code Error: %s", mysql_error(SQL));
		}

		MYSQL_RES *res = mysql_store_result(SQL);

		MYSQL_ROW row;
		while (row = mysql_fetch_row(res))
		{
			return atoi(row[0]) + 1;
		}

		return -1;
	}
}

int MySQL::generate_student_id()
{
	char cmd[1024];

	sprintf(cmd, "SELECT MAX(student_id) FROM student");

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to search Max Student_id Error: %s", mysql_error(SQL));
	}

	MYSQL_RES *res = mysql_store_result(SQL);

	MYSQL_ROW row;
	while (row = mysql_fetch_row(res))
	{
		return atoi(row[0]) + 1;
	}

	return -1;
}

int MySQL::generate_teacher_id()
{
	char cmd[1024];

	sprintf(cmd, "SELECT MAX(teacher_id) FROM teacher");

	if (mysql_query(SQL, cmd))
	{
		printf("Failed to search Max Teacher_id Error: %s", mysql_error(SQL));
	}

	MYSQL_RES *res = mysql_store_result(SQL);

	MYSQL_ROW row;
	while (row = mysql_fetch_row(res))
	{
		return atoi(row[0]) + 1;
	}

	return -1;
}

void Server_END::GetLoginPage()
{
	svr.Get("/exam-system/login", [](const httplib::Request& req, httplib::Response& res)
		{
			vector<string> response = read_file(Exam_::Html::Login);
			res.set_content(response[0], response[1]);
		});

	svr.Get("/exam-system/css/login.css", [](const httplib::Request& req, httplib::Response& res)
		{
			vector<string> response = read_file(Exam_::Css::Login);
			res.set_content(response[0], response[1]);
		});

	svr.Get("/exam-system/js/login.js", [](const httplib::Request& req, httplib::Response& res)
		{
			vector<string> response = read_file(Exam_::Js::Login);
			res.set_content(response[0], response[1]);
		});

	svr.Get("/exam-system/img/background.jpeg", [](const httplib::Request& req, httplib::Response& res)
		{
			vector<string> response = read_file(Exam_::Img::Login);
			res.set_content(response[0], response[1]);
		});

	svr.Get("/StaticImages/logo.png", [](const httplib::Request& req, httplib::Response& res)
		{
			vector<string> response = read_file(Static_::Img::Logo);
			res.set_content(response[0], response[1]);
		});

	svr.Post("/login", [](const httplib::Request& req, httplib::Response& res)
		{
			if (req.has_param("username") && req.has_param("password"))
			{
				string username = req.get_param_value("username");
				string password = req.get_param_value("password");
				MYSQL* conn = mysql_init(NULL);
				if (conn == NULL)
				{
					res.set_content("{\"status\":\"failure\",\"message\":\"Internal server error\"}", "application/json");
					res.status = 500;
					return;
				}

				if (!mysql_real_connect(conn, host, user, pw, DB_Name, port, NULL, 0))
				{
					cout << mysql_error(conn) << endl;
					res.set_content("{\"status\":\"failure\",\"message\":\"Database connection failed\"}", "application/json");
					res.status = 503;
					mysql_close(conn);
					return;
				}
				
				if (!verification(conn, username, password))
				{
					res.set_content("{\"status\":\"failure\",\"message\":\"Invalid credentials\",\"color\":\"red\"}", "application/json");
					//res.set_redirect("/login");
					res.status = 401;
				}
				else
				{
					if (!req.has_header("Cookie") || req.get_header_value("Cookie").find("authToken"))
					{
					time_t now = time(nullptr);
					time_t expire = now + 24 * 60 * 60;
					std::stringstream ss;
					ss << "Expires=" << make_date(expire) << "; ";
					string cookie = "authToken=" + username + "; " + ss.str() + "Path=/; Domain=localhost; SameSite=Lax";
					
						res.set_header("Set-Cookie", cookie);
					}
					res.set_content("{\"status\":\"success\",\"message\":\"Token OK\",\"token\":\"token\"}", "application/json");
					res.status = 200;
				}
			}
		});
}

void Server_END::GetStudentManagePage()
{
	svr.Get("/manage-system/student-manage", [](const httplib::Request& req, httplib::Response& res)
		{
			vector<string> response = read_file(Manage_::Html::StudentManage);
			res.set_content(response[0], response[1]);
		});

	svr.Get("/manage-system/css/bootstrap.min.css", [](const httplib::Request& req, httplib::Response& res)
		{
			vector<string> response = read_file(Manage_::Css::BootstrapMin);
			res.set_content(response[0], response[1]);
		});

	svr.Get("/manage-system/css/font-awesome.min.css", [](const httplib::Request& req, httplib::Response& res)
		{
			vector<string> response = read_file(Manage_::Css::FontAwesomeMin);
			res.set_content(response[0], response[1]);
		});

	svr.Get("/manage-system/css/common.css", [](const httplib::Request& req, httplib::Response& res)
		{
			vector<string> response = read_file(Manage_::Css::Common);
			res.set_content(response[0], response[1]);
		});

	svr.Get("/manage-system/css/student-manage.css", [](const httplib::Request& req, httplib::Response& res)
		{
			vector<string> response = read_file(Manage_::Css::StudentManage);
			res.set_content(response[0], response[1]);
		});

	svr.Get("/manage-system/js/jquery.min.js", [](const httplib::Request& req, httplib::Response& res)
		{
			vector<string> response = read_file(Manage_::Js::JqueryMin);
			res.set_content(response[0], response[1]);
		});

	svr.Get("/manage-system/js/bootstrap.min.js", [](const httplib::Request& req, httplib::Response& res)
		{
			vector<string> response = read_file(Manage_::Js::BootstrapMin);
			res.set_content(response[0], response[1]);
		});

	svr.Get("/manage-system/js/common.js", [](const httplib::Request& req, httplib::Response& res)
		{
			vector<string> response = read_file(Manage_::Js::Common);
			res.set_content(response[0], response[1]);
		});

	svr.Get("/manage-system/js/student-manage.js", [](const httplib::Request& req, httplib::Response& res)
		{
			vector<string> response = read_file(Manage_::Js::StudentManage);
			res.set_content(response[0], response[1]);
		});

	svr.Get("/manage-system/js/member-manage.js", [](const httplib::Request& req, httplib::Response& res)
		{
			vector<string> response = read_file(Manage_::Js::MemberManage);
			res.set_content(response[0], response[1]);
		});

	svr.Get("/manage-system/fonts/fontawesome-webfont.woff2", [](const httplib::Request& req, httplib::Response& res)
		{
			vector<string> response = read_file(Manage_::Font::FontAwesome);
			res.set_content(response[0], response[1]);
		});

	svr.Get("/student-manage/getstudent", [](const httplib::Request& req, httplib::Response& res)
		{
			MySQL data;
			vector<Student> students = data.search_stu();
			string json = "[";
			for (auto student : students) {
				json.append("{\"studentNum\":\"" + to_string(student.id) + "\",\"studentName\":\"" + student.name + "\",\"studentMajor\":\"" + student.pro + "\"},");
			}
			json[json.length() - 1] = ']';
			res.set_content(json, "application/json");
		});

	svr.Post("/student-manage/addstudent", [](const httplib::Request& req, httplib::Response& res)
		{
			if (req.has_param("studentNum") && req.has_param("studentName") && req.has_param("studentMajor"))
			{
				string id = req.get_param_value("studentNum");
				string name = req.get_param_value("studentName");
				string major = req.get_param_value("studentMajor");
				MYSQL* conn = mysql_init(NULL);
				if (conn == NULL)
				{
					res.set_content("{\"status\":\"failure\",\"message\":\"Internal server error\"}", "application/json");
					res.status = 500;
					return;
				}

				if (!mysql_real_connect(conn, host, user, pw, DB_Name, port, NULL, 0))
				{
					cout << mysql_error(conn) << endl;
					res.set_content("{\"status\":\"failure\",\"message\":\"Database connection failed\"}", "application/json");
					res.status = 503;
					mysql_close(conn);
					return;
				}

				if (name.empty()) {
					res.set_content("{\"status\":\"success\",\"message\":\"Invalid Student Name\"}", "application/json");
					return;
				}

				MySQL data;

				if (!data.search_stu(atoi(id.c_str())).empty())
				{
					res.set_content("{\"status\":\"success\",\"message\":\"Student ID exsits\"}", "application/json");
					return;
				}
				else
				{
					res.set_content("{\"status\":\"success\",\"message\":\"Student ID Involved\"}", "application/json");
					res.status = 200;
					Student student(atoi(id.c_str()), name, "123456", major, 6);
					data.insert_sql(student);
				}
			}
		});

	svr.Post("/student-manage/deletestudent", [](const httplib::Request& req, httplib::Response& res)
		{
			string id = req.get_param_value("studentNum");
			MYSQL* conn = mysql_init(NULL);
			if (conn == NULL)
			{
				res.set_content("{\"status\":\"failure\",\"message\":\"Internal server error\"}", "application/json");
				res.status = 500;
				return;
			}

			if (!mysql_real_connect(conn, host, user, pw, DB_Name, port, NULL, 0))
			{
				cout << mysql_error(conn) << endl;
				res.set_content("{\"status\":\"failure\",\"message\":\"Database connection failed\"}", "application/json");
				res.status = 503;
				mysql_close(conn);
				return;
			}

			MySQL data;
			if (data.search_stu(atoi(id.c_str())).empty())
			{
				res.set_content("{\"status\":\"success\",\"message\":\"Student ID not exist\"}", "application/json");
				return;
			}
			else
			{
				res.set_content("{\"status\":\"success\",\"message\":\"Student ID Deleted\"}", "application/json");
				res.status = 200;
				data.delete_stu(atoi(id.c_str()));
			}
		});

	svr.Post("/student-manage/deletestudents", [](const httplib::Request& req, httplib::Response& res)
		{
			string json = req.body;
			MYSQL* conn = mysql_init(NULL);
			if (conn == NULL)
			{
				res.set_content("{\"status\":\"failure\",\"message\":\"Internal server error\"}", "application/json");
				res.status = 500;
				return;
			}

			if (!mysql_real_connect(conn, host, user, pw, DB_Name, port, NULL, 0))
			{
				cout << mysql_error(conn) << endl;
				res.set_content("{\"status\":\"failure\",\"message\":\"Database connection failed\"}", "application/json");
				res.status = 503;
				mysql_close(conn);
				return;
			}

			res.set_content("{\"status\":\"success\",\"message\":\"Students IDs Deleted\"}", "application/json");
			res.status = 200;
			MySQL data;
			vector<string> IDs = get_all_from_json("id", json);
			for (auto id : IDs)
				data.delete_stu(atoi(id.c_str()));
		});
}

string aa;

void Server_END::GetStudentPage() 
{
	svr.Get("/exam-system/student", [](const httplib::Request& req, httplib::Response& res)
		{
			if (req.has_header("Cookie") && req.get_header_value("Cookie").find("authToken") != string::npos)
			{
				vector<string> response = read_file(Exam_::Html::Student);
				res.set_content(response[0], response[1]);
			}
			else {
				res.set_redirect("/login");
			}
		});

	svr.Get("/exam-system/css/bootstrap.min.css", [](const httplib::Request& req, httplib::Response& res)
		{
			vector<string> response = read_file(Exam_::Css::BootstrapMin);
			res.set_content(response[0], response[1]);
		});

	svr.Get("/exam-system/css/ionicons.min.css", [](const httplib::Request& req, httplib::Response& res)
		{
			vector<string> response = read_file(Exam_::Css::IoniconsMin);
			res.set_content(response[0], response[1]);
		});

	svr.Get("/exam-system/css/font-awesome.min.css", [](const httplib::Request& req, httplib::Response& res)
		{
			vector<string> response = read_file(Exam_::Css::FontAwesomeMin);
			res.set_content(response[0], response[1]);
		});

	svr.Get("/exam-system/css/common.css", [](const httplib::Request& req, httplib::Response& res)
		{
			vector<string> response = read_file(Exam_::Css::Common);
			res.set_content(response[0], response[1]);
		});

	svr.Get("/exam-system/js/jquery.min.js", [](const httplib::Request& req, httplib::Response& res)
		{
			vector<string> response = read_file(Exam_::Js::JqueryMin);
			res.set_content(response[0], response[1]);
		});

	svr.Get("/exam-system/js/bootstrap.min.js", [](const httplib::Request& req, httplib::Response& res)
		{
			vector<string> response = read_file(Exam_::Js::BootstrapMin);
			res.set_content(response[0], response[1]);
		});

	svr.Get("/exam-system/js/common.js", [](const httplib::Request& req, httplib::Response& res)
		{
			vector<string> response = read_file(Exam_::Js::Common);
			res.set_content(response[0], response[1]);
		});

	svr.Get("/exam-system/js/student.js", [](const httplib::Request& req, httplib::Response& res)
		{
			vector<string> response = read_file(Exam_::Js::Student);
			res.set_content(response[0], response[1]);
		});

	svr.Get("/exam-system/fonts/fontawesome-webfont.woff2", [](const httplib::Request& req, httplib::Response& res)
		{
			vector<string> response = read_file(Exam_::Font::FontAwesome);
			res.set_content(response[0], response[1]);
		});

	svr.Get("/exam-system/fonts/ionicons.woff", [](const httplib::Request& req, httplib::Response& res)
		{
			vector<string> response = read_file(Exam_::Font::Ionicons);
			res.set_content(response[0], response[1]);
		});

	svr.Get("/StaticImages/client.png", [](const httplib::Request& req, httplib::Response& res)
		{
			vector<string> response = read_file(Static_::Img::Client);
			res.set_content(response[0], response[1]);
		});

	svr.Post("/student/adduserinfo", [](const httplib::Request& req, httplib::Response& res)
		{
			aa = req.get_header_value("Cookie");
			res.set_content("{\"status\":\"failure\",\"message\":\"Yes I'm\"}", "application/json");
		});

}

void Server_END::GetSubjectManagePage()
{
	svr.Get("/manage-system/subject-manage", [](const httplib::Request& req, httplib::Response& res)
		{
			vector<string> response = read_file(Manage_::Html::SubjectManage);
			res.set_content(response[0], response[1]);
		});

	svr.Get("/manage-system/css/bootstrap.min.css", [](const httplib::Request& req, httplib::Response& res)
		{
			vector<string> response = read_file(Manage_::Css::BootstrapMin);
			res.set_content(response[0], response[1]);
		});

	svr.Get("/manage-system/css/ionicons.min.css", [](const httplib::Request& req, httplib::Response& res)
		{
			vector<string> response = read_file(Manage_::Css::IoniconsMin);
			res.set_content(response[0], response[1]);
		});

	svr.Get("/manage-system/css/font-awesome.min.css", [](const httplib::Request& req, httplib::Response& res)
		{
			vector<string> response = read_file(Manage_::Css::FontAwesomeMin);
			res.set_content(response[0], response[1]);
		});

	svr.Get("/manage-system/css/common.css", [](const httplib::Request& req, httplib::Response& res)
		{
			vector<string> response = read_file(Manage_::Css::Common);
			res.set_content(response[0], response[1]);
		});

	svr.Get("/manage-system/css/subject-library.css", [](const httplib::Request& req, httplib::Response& res)
		{
			vector<string> response = read_file(Manage_::Css::SubjectLibrary);
			res.set_content(response[0], response[1]);
		});
	
	svr.Get("/manage-system/js/jquery.min.js", [](const httplib::Request& req, httplib::Response& res)
		{
			vector<string> response = read_file(Manage_::Js::JqueryMin);
			res.set_content(response[0], response[1]);
		});

	svr.Get("/manage-system/js/bootstrap.min.js", [](const httplib::Request& req, httplib::Response& res)
		{
			vector<string> response = read_file(Manage_::Js::BootstrapMin);
			res.set_content(response[0], response[1]);
		});

	svr.Get("/manage-system/js/common.js", [](const httplib::Request& req, httplib::Response& res)
		{
			vector<string> response = read_file(Manage_::Js::Common);
			res.set_content(response[0], response[1]);
		});

	svr.Get("/manage-system/js/subject-library-add.js", [](const httplib::Request& req, httplib::Response& res)
		{
			vector<string> response = read_file(Manage_::Js::SubjectLibraryAdd);
			res.set_content(response[0], response[1]);
		});

	svr.Get("/manage-system/js/subject-library.js", [](const httplib::Request& req, httplib::Response& res)
		{
			vector<string> response = read_file(Manage_::Js::SubjectLibrary);
			res.set_content(response[0], response[1]);
		});

	svr.Get("/manage-system/fonts/ionicons.woff", [](const httplib::Request& req, httplib::Response& res)
		{
			vector<string> response = read_file(Manage_::Font::Ionicons);
			res.set_content(response[0], response[1]);
		});

	svr.Get("/manage-system/fonts/fontawesome-webfont.woff2", [](const httplib::Request& req, httplib::Response& res)
		{
			vector<string> response = read_file(Manage_::Font::FontAwesome);
			res.set_content(response[0], response[1]);
		});

	svr.Post("/fileupload", [](const httplib::Request& req, httplib::Response& res)
		{
			cout << 1 << endl;
		});
}



void listening() {
	Server_END ser;
	ser.GetLoginPage();
	ser.GetStudentManagePage();
	ser.GetStudentPage();
	ser.GetSubjectManagePage();
	ser.svr.listen("127.0.0.1", 8000);
}

int main()
{
	MySQL data;
	//Object_Sub o = Object_Sub(10003,10001,"计算机科学与技术", "test", "A", "B", "C", "D", 1);
	//Subject_Sub sub = Subject_Sub(20001,10001, "计算机科学与技术", "test");
	//Student_ob_ans obans = Student_ob_ans(10001, 10110001, 2);
	//Student_sub_ans subans = Student_sub_ans(10001, 10120002, "test");
	//Date day = Date(1, 1, 1, 1, 1);
	//string sublist = data.generate_sub_list("软件工程");
	//string studentlist = "10001,10010";
	//Exam ex = Exam(1, sublist, studentlist, "软件工程", 1, day);
	thread thread1(listening);

	cout << aa;

	thread1.join();
}

